﻿using System;
using System.Collections.Generic;
using System.Text;
using WordService.Data.IDAL;
using System.Data.SqlClient;
using WordService.DBUtil;
using System.Data;
using WordService.Model;

namespace WordService.Data.DAL
{
    public sealed class AuthorDAL : IAuthorDAL
    {
        /// <summary>
        /// 插入作者的SQL
        /// </summary>
        private const String AUTHOR_INSERT = @""
            + "INSERT INTO BAS_AUTHOR(NAME,SEQUENCE,ARTICLE_ID,RIGHTRATE,TOTALCOUNT) VALUES (@Name,@Sequence,@ArticleID,@RightRate,@TotalCount) SELECT @@IDENTITY AS ID;";
        /// <summary>
        /// 根据作者名称判断该作者是否存在
        /// </summary>
        private const String AUTHOR_EXIST = @""
            + "SELECT COUNT(*) FROM BAS_AUTHOR WHERE NAME=@Name;";
        /// <summary>
        /// 根据提供的信息更新作者的整体信息
        /// </summary>
        private const String AUTHOR_UPDATE = @""
            + "UPDATE BAS_AUTHOR SET NAME=@Name,SEQUENCE=@Sequence,ARTICLE_ID=@ArticleID,RIGHTRATE=@RightRate,TOTALCOUNT=@TotalCount WHERE ID=@ID";
        /// <summary>
        /// 根据作者名称去获取一个作者的整体模型
        /// </summary>
        private const String AUTHOR_QUERY_BY_NAME = @""
            + "SELECT * FROM BAS_AUTHOR WHERE NAME=@Name;";

        private const String REL_AUTHOR_INSTITUTION_INSERT = @""
            + "INSERT INTO REL_AUTHOR_INSTITUTION(AUTHOR_ID,INSTITUTION_ID) VALUES (@AuthorID,@InstitutionID);";
        private const String AUTHOR_QUERY = @"SELECT * FROM BAS_AUTHOR"; 
        #region 目前不开放的接口，主要用于内部私有
        /// <summary>
        /// 判断作者是否存在
        /// </summary>
        /// <param name="name">作者姓名</param>
        /// <returns>存在为true，不存在为false</returns>
        private Boolean ExistAuthor(String name)
        {
            SqlParameter[] authorParms = new SqlParameter[] { 
                new SqlParameter("@Name",name)
            };
            object number = DBUtility.ExecuteScalar(
                DBUtility.ConnectionStr,
                CommandType.Text,
                AUTHOR_EXIST,
                authorParms
                );
            // 判断是否存在
            if (number == DBNull.Value || number == null || Convert.ToInt64(number) == 0)
            {
                return false;
            }
            else
            {
                return true;// QueryByName(name) == null ? 0 : QueryByName(name).ID;
            }
        }
        /// <summary>
        /// 根据作者姓名获取作者的模型
        /// </summary>
        /// <param name="name">作者姓名</param>
        /// <returns>返回该作者的模型，如果不存在返回null</returns>
        private Author QueryByName(String name)
        {
            Author author = new Author();
            SqlParameter[] authorParams = new SqlParameter[] { new SqlParameter("@Name", name) };
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                AUTHOR_QUERY_BY_NAME,
                authorParams
                ))
            {
                if (dr.Read())
                {
                    author.Name = dr["NAME"].ToString();
                    author.ID = Convert.ToInt64(dr["ID"]);
                    author.RightRate = Convert.ToDouble(dr["RIGHTRATE"]);
                    author.TotalCount = Convert.ToInt64(dr["TOTALCOUNT"]);
                    author.Sequence = Convert.ToInt32(dr["SEQUENCE"]);
                }
            };
            return author;
        }
        #endregion
        /// <summary>
        /// 插入作者的数据层接口【实现部分】
        /// </summary>
        /// <param name="name">作者姓名</param>
        /// <param name="sequence">作者顺序</param>
        /// <param name="articleID">对应文章ID</param>
        /// <param name="rightRate">作者的权值</param>
        /// <param name="totalCount">作者总共出现的次数</param>
        /// <returns>返回插入的作者的ID号</returns>
        public Int64 InsertAuthor(String name, Int32 sequence, Int64 articleID, decimal rightRate, Int64 totalCount)
        {
            //初始参数
            SqlParameter[] authorParms = new SqlParameter[]{
                new SqlParameter("@Name",name),
                new SqlParameter("@Sequence",sequence),               
                new SqlParameter("@ArticleID",articleID),
                new SqlParameter("@RightRate",rightRate),
                new SqlParameter("@TotalCount",totalCount)
            };
            // 初始化连接
            Int64 ID = 0L;
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                AUTHOR_INSERT,
                authorParms))
            {
                while (dr.Read())
                {
                    ID = Convert.ToInt64(dr["ID"]);
                }
            }
            // 获取返回ID
            return ID;
        }
        /// <summary>
        /// 保存作者的数据层接口【添加、更新】【实现部分】
        /// </summary>
        /// <param name="name">作者姓名</param>
        /// <param name="sequence">作者顺序</param>
        /// <param name="articleID">对应文章ID</param>
        /// <param name="rightRate">作者的权值</param>
        /// <param name="totalCount">作者总共出现的次数</param>
        /// <returns>返回保存的作者ID号</returns>
        public Int64 SaveAuthor(String name, Int32 sequence, Int64 articleID, decimal rightRate, Int64 totalCount)
        {
            Boolean result = this.ExistAuthor(name);
            if (result)
            {
                Author author = this.QueryByName(name);
                if (author != null)
                {
                    return UpdateAuthor(name, sequence, articleID, rightRate, (author.TotalCount + 1), author.ID);
                }
                else
                {
                    return InsertAuthor(name, sequence, articleID, rightRate, totalCount);
                }
            }
            else
            {
                return InsertAuthor(name, sequence, articleID, rightRate, totalCount);
            }
        }
        /// <summary>
        /// 更新作者的数据层接口【实现部分】
        /// </summary>
        /// <param name="name">作者姓名</param>
        /// <param name="sequence">作者顺序</param>
        /// <param name="articleID">对应文章ID</param>
        /// <param name="rightRate">作者的权值</param>
        /// <param name="totalCount">作者总共出现的次数</param>
        /// <param name="ID">作者的ID号</param>
        /// <returns>返回保存的作者的ID号</returns>
        public Int64 UpdateAuthor(String name, Int32 sequence, Int64 articleID, decimal rightRate, Int64 totalCount, Int64 ID)
        {
            SqlParameter[] authorParms = new SqlParameter[]{
                new SqlParameter("@ID",ID),
                new SqlParameter("@Name",name),
                new SqlParameter("@Sequence",sequence),
                new SqlParameter("@ArticleID",articleID),
                new SqlParameter("@RightRate",rightRate),
                new SqlParameter("@TotalCount",totalCount)
            };

            DBUtility.ExecuteNonQuery(
                DBUtility.ConnectionStr,
                CommandType.Text,
                AUTHOR_UPDATE,
                authorParms
                );
            return ID;
        }
        /// <summary>
        /// 插入作者和机构的关联关系
        /// </summary>
        /// <param name="authorID"></param>
        /// <param name="institutionID"></param>
        /// <returns></returns>
        public Boolean InsertRelationAI(Int64 authorID, Int64 institutionID)
        {
            Boolean result = false;
            try
            {
                SqlParameter[] relParms = new SqlParameter[]{
                    new SqlParameter("@InstitutionID",institutionID),
                    new SqlParameter("@AuthorID",authorID)
                };
                DBUtility.ExecuteNonQuery(
                    DBUtility.ConnectionStr,
                    CommandType.Text,
                    REL_AUTHOR_INSTITUTION_INSERT,
                    relParms
                    );
                result = true;
            }
            catch (Exception)
            {
                result = false;
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="keywordName"></param>
        /// <param name="insName"></param>
        /// <param name="frequency"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public IList<Model.Author> QueryByCondition(string keywordName, string insName, long frequency, string year)
        { 
            #region 动态生成SQL语句
            IList<String> sqlList = new List<String>();
            if (year != null && !year.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_ARTICLE WHERE PUBLISHDATE LIKE '%" + year + "%'))");
            }
            if (frequency != 0)
            {
                sqlList.Add("(TOTALCOUNT > " + frequency + ")");
            }
            if (keywordName != null && !keywordName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_KEYWORD WHERE NAME LIKE '%" + keywordName + "%'))");
            }
            if (insName != null && !insName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_INSTITUTION WHERE NAME LIKE '%" + insName + "%'))");
            }
            StringBuilder builder = new StringBuilder();
            builder.Append(AUTHOR_QUERY);
            builder.Append(DBUtility.GenerateSQLCondition(sqlList));
            #endregion

            IList<Author> authorList = new List<Author>();
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                builder.ToString()))
            {
                while (dr.Read())
                {
                    Author author = new Author();
                    author.ID = Convert.ToInt64(dr["ID"]);
                    author.Name = dr["NAME"].ToString();
                    author.Sequence = Convert.ToInt32(dr["SEQUENCE"]);
                    author.RightRate = Convert.ToDouble(dr["RIGHTRATE"]);
                    author.TotalCount = Convert.ToInt64(dr["TOTALCOUNT"]);
                    Article article = new Article();
                    article.ID = Convert.ToInt64(dr["ARTICLE_ID"]);
                    author.Article = article;
                    authorList.Add(author);
                }
            }
            return authorList;
        }
    }
}
